﻿using System;
using System.Windows;
using Microsoft.Win32;
using System.Data.OleDb;
using System.Diagnostics;
using Warehouse.DataModel;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Data.Objects.DataClasses;
using System.Data.Entity.Design.PluralizationServices;
using System.Globalization;
using System.Windows.Controls;
using System.IO;
using System.Windows.Media;
using Excel = Microsoft.Office.Interop.Excel;

namespace DataImport
{
    public static class Extensions
    {
        public static T FieldByLooseName<T>(this IDataReader row, string name)
        {
            string value = null;
            for (var i = 0; i < row.FieldCount; i++)
            {
                var column = row.GetName(i);
                if (name == column.Trim())
                {
                    value = row[column].ToString();
                    break;
                }
            }
            var str = value.StartsWith("$") ? value.Substring(1) : value;
            return (T)Convert.ChangeType(str,  typeof(T));
        }

        public static decimal GetShopPrice(this IDataReader row) { return row.FieldByLooseName<decimal>("Shop"); }
        public static decimal GetShipping(this IDataReader row) { return row.FieldByLooseName<decimal>("Shipping"); }
        public static decimal GetMinRetailCost(this IDataReader row) { return row.FieldByLooseName<decimal>("Min"); }
    }

    public partial class MainWindow : Window
    {
        Repository repository = new Repository();
        Func<string, string> pluralize = PluralizationService.CreateService(new CultureInfo("en-US")).Pluralize;

        public MainWindow()
        {
            InitializeComponent();

            var button2Action = new[]
            {
                Tuple.Create<Button, Action>( this.BrandsButton, this.Brands ),
                Tuple.Create<Button, Action>( this.ClientsButton, this.Clients ),
                Tuple.Create<Button, Action>( this.VendorsButton, this.Vendors ),
            };

            this.ProductPerTabDelimitedFileButton.Click += delegate
            {
                this.ProductPerTabDelimiteFile();
            };

            Array.ForEach(button2Action, x =>
            {
                x.Item1.Click += delegate { x.Item2(); };
                this.AllButton.Click += delegate { x.Item2(); };
            });

            this.Title = string.Format(
                "Server: {0}. Database: {1}.", repository.Connection.DataSource, repository.Connection.Database);
        }

        void Brands()
        {
            Func<IDataReader, Brand> processRow = reader =>
            {
                var brand = new Brand
                {
                    Name = reader["Brand"].ToString(),
                    ClientDefaultRegularProductCommission = Convert.ToDecimal(reader["Regular-C"].ToString()),
                    VendorDefaultRegularProductCommission = Convert.ToDecimal(reader["Regular-V"].ToString()),
                };

                brand.ClientDefaultLitrSizeProductCommission = reader["Litter-C"] == DBNull.Value ? brand.ClientDefaultRegularProductCommission : Convert.ToDecimal(reader["Litter-C"]);
                brand.VendorDefaultLitrSizeProductCommission = reader["Litter-V"] == DBNull.Value ? brand.VendorDefaultRegularProductCommission : Convert.ToDecimal(reader["Litter-V"]);
                return brand;
            };

            FromExcelFile(processRow, "Sheet1");
        }

        Product ProcessProductRow(IDataReader reader)
        {
            var brandName = reader["Brand"].ToString().Trim();
            var brand = repository.Brands
                .Where(b => b.Name == brandName)
                .FirstOrDefault();
            if (brand == null)
            {
                var message = "Can not find brand: " + brandName;
                throw new InvalidOperationException(message);
            }
            var productName = reader["Product"].ToString().Trim();
            var productNameEntity =
                brand.ProductNames.Where(x => x.Name == productName).FirstOrDefault() ??
                new ProductName { Name = productName, Brand = brand };

            Product product = null;
            var SKU = reader["SKU"].ToString().Trim();
            string upc = null;
            try 
            { 
                upc = reader["UPC"].ToString().Trim();
                if (string.IsNullOrEmpty(upc)) throw new Exception();
                product = repository.Products.First(p => p.SKU == upc);
                product.UPCs.Add(new UPC { Code = SKU, UpcNote = repository.NewPackage });
            }
            catch 
            { 
                upc = SKU;
                product = new Product
                {
                    ProductName = productNameEntity,
                    ShopCost = reader.GetShopPrice(),
                    SKU = SKU,
                    ShippingCost = reader.GetShipping(),
                    MinRetailCost = reader.GetMinRetailCost(),
                };
                var sizeColumnValue = reader["Size"].ToString();
                decimal size = Decimal.MinValue;
                var isNumber = decimal.TryParse(sizeColumnValue, out size);
                var isColor = true;
                try { isColor = ColorConverter.ConvertFromString(sizeColumnValue) != null; } catch { isColor = false; }

                if (productName.ToLower().Contains("gallon") || sizeColumnValue.ToLower().Contains("gallon") || sizeColumnValue.ToLower().Contains("Gallan"))
                {
                    product.SizeOrColor = "1";
                    product.Unit = repository.Gallon;
                }
                else if (productName.ToLower().Contains("set") || sizeColumnValue.ToLower().Contains("set"))
                {
                    product.Unit = repository.Set;
                    product.SizeOrColor = isNumber ? sizeColumnValue : "1";
                }
                else if (isNumber)
                {
                    product.Unit = repository.Oz;
                    product.SizeOrColor = sizeColumnValue;
                }
                else if (isColor)
                {
                    product.Unit = repository.Color;
                    product.SizeOrColor = sizeColumnValue;
                }
                else //kit
                {
                    product.Unit = repository.Kit;
                    product.SizeOrColor = sizeColumnValue;
                }

                product.UPCs.Add(new UPC
                {
                    Code = upc,
                    UpcNote = repository.NewPackage,
                });
            }

            return product;
        } 

        void Products()
        {
            FromExcelFile(ProcessProductRow);
        }

        void Clients()
        {
            FromExcelFile(reader =>
                new Client
                {
                    CompanyName = reader["Company Name"].ToString(),
                    BillToAddress = reader["Address"].ToString(),
                    BillToCity = reader["City"].ToString(),
                    BillToState = reader["State"].ToString(),
                    BillToZipCode = reader["Zip Code"].ToString(),
                    ShipToAddress = reader["Address"].ToString(),
                    ShipToCity = reader["City"].ToString(),
                    ShipToState = reader["State"].ToString(),
                    ShipToZipCode = reader["Zip Code"].ToString(),
                    EMail = reader["Email Address"].ToString(),
                    Attn = reader["ATT:"].ToString(),
                    Phone = reader["Phone Number"].ToString(),
                    Fax = reader["Fax Number"].ToString(),
                },
                "Clients");
        }

        void Vendors()
        {
            FromExcelFile(reader =>
                new Vendor
                {
                    CompanyName = reader["Company Name"].ToString(),
                    Address = reader["Address"].ToString(),
                    City = reader["City"].ToString(),
                    State = reader["State"].ToString(),
                    ZipCode = reader["Zip Code"].ToString(),
                    EMail = reader["Email Address"].ToString(),
                    Attn = reader["ATT:"].ToString(),
                    Phone = reader["Phone Number"].ToString(),
                },
                "Vendors");
        }

        #region Excel helpers

        void FromFile<T, TConnection>(Func<string, IEnumerable<IDbCommand>> getCommands, Func<IDataReader, T> processRow, string entitySetName = null) 
            where T : EntityObject
            where TConnection : IDbConnection
        {
            var dlg = new OpenFileDialog 
            { 
                DefaultExt = ".xlsx",
                Filter = "Text documents (.txt)|*.txt|Excel files (.xlsx)|*.xlsx",
                InitialDirectory = Process.GetCurrentProcess().StartInfo.WorkingDirectory,
                Title = "Processing " + typeof(T).Name,
                Multiselect = true,
            };
            if (dlg.ShowDialog() == true)
            {
                Erros.Items.Clear();
                foreach (var file in dlg.FileNames)
                {

                    foreach (var cmd in getCommands(file))
                    {
                        using (IDataReader reader = cmd.ExecuteReader())
                            while (reader.Read())
                            {
                                repository.Dispose();
                                repository = new Repository();
                                T entity = default(T);
                                try
                                {
                                    entity = processRow(reader);
                                    if (entity != null)
                                    {
                                        if (entity.EntityState == EntityState.Detached) repository.AddObject(pluralize(typeof(T).Name), entity);
                                        repository.SaveChanges();
                                    }
                                }
                                catch (Exception why)
                                {
                                    try
                                    {
                                        var values = new object[reader.FieldCount];
                                        var i = reader.GetValues(values);
                                        Debug.Assert(reader.FieldCount == i);
                                        string aggregatedMessage = "";
                                        do
                                        {
                                            aggregatedMessage += why.Message;
                                            why = why.InnerException;
                                        }
                                        while (why != null);

                                        var tuple = Tuple.Create(aggregatedMessage, String.Join(",", Array.ConvertAll(values, x => x.ToString())));
                                        Erros.Items.Add(tuple);
                                    }
                                    catch { }
                                }
                            }
                    }

                }

                if (Erros.Items.Count > 0)
                {
                    File.AppendAllLines(
                        path: Path.Combine(Path.GetDirectoryName(dlg.FileName), "Errors.log"),
                        contents: from Tuple<string, string> x in Erros.Items select x.ToString()
                    );
                }
            }
        }

        void FromExcelFile<T>(Func<IDataReader, T> processRow, string specificSheet = null) where T : EntityObject
        {
            FromFile<T, OleDbConnection>(
                file => 
                {
                    var conn = new OleDbConnection
                    {
                        ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"",file)
                    };
                    conn.Open();
                    if (specificSheet != null)
                        return new[]
                            {
                                new OleDbCommand(string.Format("SELECT * FROM [{0}$]", specificSheet), conn)
                            };
                    else
                        return
                            from table in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).AsEnumerable()
                            select new OleDbCommand
                            {
                                Connection = conn,
                                CommandText = string.Format("SELECT * FROM [{0}]", table["TABLE_NAME"])
                            };
                }, 
                processRow);
        }

        void ProductPerTabDelimiteFile()
        {
            FromFile<Product, OleDbConnection>(
                file =>
                {
                    var folder = Path.GetDirectoryName(file);
                    var name = Path.GetFileName(file);
                    File.WriteAllLines(
                        path: Path.Combine(folder, "schema.ini"),
                        contents: new[] 
                        {
                            "[" + name + "]",
                            "Format = TabDelimited",
                            "MaxScanRows=0",
                        }
                    );
                    var content = File.ReadAllLines(file).Where(s => s.Trim() != "");
                    File.WriteAllLines(file, content);
                    var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Text;HDR=Yes;FMT=Delimited;IMEX=1\"", folder);
                    var conn = new OleDbConnection(connectionString);
                    conn.Open();
                    return new[] 
                    { 
                        new OleDbCommand
                        {
                            Connection = conn,
                            CommandText = string.Format("SELECT * FROM [{0}]", name)
                        }
                    };
                },
                ProcessProductRow);
        }

        #endregion

        private void Excel2TabDelimited_Click(object sender, RoutedEventArgs e)
        {
            var dlg = new OpenFileDialog
            {
                DefaultExt = ".xlsx",
                Filter = "Excel files (.xlsx)|*.xlsx|Text documents (.txt)|*.txt",
            };
            if (dlg.ShowDialog() == true)
            {
                var folder = new DirectoryInfo(Path.Combine(Path.GetDirectoryName(dlg.FileName), "Products"));
                folder.Create();
                Excel.Application app = null;
                try {
                    app = new Excel.Application();
                    app.Workbooks.Open(dlg.FileName);
                    foreach (Excel.Worksheet sheet in app.ActiveWorkbook.Worksheets)
                    {
                        sheet.SaveAs(
                            Filename: Path.Combine(folder.FullName, sheet.Name)
                            ,FileFormat: Excel.XlFileFormat.xlTextWindows
                        );
                    }
                    app.ActiveWorkbook.Close(SaveChanges: false);
                }
                finally {
                    if (app != null) app.Quit();
                }
            }
        }

        private void SortWorsheets_Click(object sender, RoutedEventArgs e)
        {
            var dlg = new OpenFileDialog
            {
                DefaultExt = ".xlsx",
                Filter = "Excel files (.xlsx)|*.xlsx|Text documents (.txt)|*.txt",
            };
            if (dlg.ShowDialog() == true)
            {
                var folder = Path.GetDirectoryName(dlg.FileName);
                Excel.Application app = null;
                try
                {
                    app = new Excel.Application();
                    app.Workbooks.Open(dlg.FileName);
                    var sorted = (from Excel.Worksheet sheet in app.ActiveWorkbook.Worksheets orderby sheet.Name select sheet).ToArray();
                    sorted[0].Move(Before: app.ActiveWorkbook.Worksheets[1]);
                    var prev = sorted[0];
                    foreach(var sheet in sorted.Skip(1))
                    {
                        sheet.Move(After: prev);
                        prev = sheet;
                    }
                    app.ActiveWorkbook.Save();
                    app.ActiveWorkbook.Close();
                }
                finally
                {
                    if (app != null) app.Quit();
                }
            }
        }
    }
}
